package com.geping.etl.common.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import com.geping.etl.common.entity.Sys_Org;

@Repository
public interface Sys_OrgRepository extends PagingAndSortingRepository<Sys_Org, Integer>,JpaSpecificationExecutor<Sys_Org>{

	//查询所有未删除的机构
	@Query("select s from Sys_Org s")
	public List<Sys_Org> findAllSysOrg();
	
	//根据机构名称模糊查询机构信息
	@Query("select s from Sys_Org s where s.enabled = 'Y' and s.orgName like concat('%',?1,'%') ")
	public List<Sys_Org> getSys_UserByLikeOrgName(String orgName);
	
	//根据机构名称和机构编号是否存在同名的机构信息
	@Query("select s from Sys_Org s where s.enabled = 'Y' and s.orgName = ?1 or s.orgId = ?2")
	public List<Sys_Org> getOrgNameAndOrgId(String orgName,String orgId);

	@Query("select s from Sys_Org s where s.enabled = 'Y' and s.orgLayer = ?3 and (s.orgName = ?1 or s.orgId = ?2)")
	List<Sys_Org> getOrgNameAndOrgIdAndSubjectId(String orgName,String orgId, Integer subjectId);
	
	//修改机构信息
	@Modifying
	@Query("update Sys_Org s set s.orgId=?2,s.orgName=?3,s.orgSortName=?4,s.orgParentId=?5,s.orgRegion=?6,s.isBussiness=?7,s.isHead=?8,s.orgLevel=?9,s.orgInsideCode = ?10,s.licenseNumber = ?11,s.option_ = ?12,s.tel = ?13,s.bankBankNumber = ?14, s.orgLayer = ?15  where s.id=?1")
	public int updateSys_Org(Integer id,String orgId,String orgName,String orgSortName,String orgParentId,String orgRegion,String isBussiness,String isHead,String orgLevel,String orgInsideCode,String licenseNumber,String option_,String tel,String bankbanknumber, Integer systemId);

	@Modifying
	@Query("update Sys_Org s set s.orgId=?2,s.orgName=?3,s.orgSortName=?4,s.orgParentId=?5,s.orgRegion=?6,s.isBussiness=?7,s.isHead=?8,s.orgLevel=?9,s.orgInsideCode = ?10,s.licenseNumber = ?11,s.option_ = ?12,s.tel = ?13,s.bankBankNumber = ?14  where s.id=?1")
	public int updateSys_Org2(Integer id,String orgId,String orgName,String orgSortName,String orgParentId,String orgRegion,String isBussiness,String isHead,String orgLevel,String orgInsideCode,String licenseNumber,String option_,String tel,String bankbanknumber);
	//逻辑删除机构
	@Modifying
	@Query("update Sys_Org s set s.enabled = ?2 where s.id = ?1")
	public int deleteSys_Org(Integer id,String enabled);
	
	 // 查询条数
   	@Query("select count(s.id) from Sys_Org s where s.orgId = ?1")
	public Integer selectCountById(String orgId);
	
	//根据机构代码查机构
	@Query("select s from Sys_Org s where s.orgId =?1")
	public List<Sys_Org> selectOrgById(String orgId);

	@Query("select s.licenseNumber from Sys_Org  s where  s.orgId=?1")
	public String selectLicenseById(String orgId);

	//根据系统查询机构
	@Query("select s from Sys_Org s where s.orgLayer = ?1 ")
	List<Sys_Org> findBySubjectId(Integer subjectId);

}
